using Microsoft.Data.Sqlite;
using System.Configuration;
using System.Data;
using SQLitePCL;

namespace Ocean.Transfer.Tools.Data
{
    using System;
    using System.Collections.Generic;

    public class SQLiteHelper : IDisposable
    {
        private readonly string _connectionString;
        private SqliteConnection _connection;
        private bool _disposed = false;

        // 静态构造函数，确保原生库只初始化一次
        static SQLiteHelper()
        {
            InitializeNativeLibrary();
        }

        public SQLiteHelper()
        {
            string databasePath = ConfigurationManager.ConnectionStrings["SQLiteConnection"].ConnectionString;
             _connectionString = $"Data Source={databasePath}";
            _connection = new SqliteConnection(_connectionString);
            TestConnection();
        }

        public SQLiteHelper(string databasePath)
        {
            _connectionString = $"Data Source={databasePath}";
            _connection = new SqliteConnection(_connectionString);
            TestConnection();
        }

        /// <summary>
        /// 初始化 SQLite 原生库
        /// </summary>
        private static void InitializeNativeLibrary()
        {
            try
            {
                // 方法1: 使用 Batteries_V2.Init() - 自动加载合适的原生库
                Batteries_V2.Init();
                Console.WriteLine("✅ SQLite 原生库初始化成功 (Batteries_V2)");
            }
            catch (Exception ex1)
            {
                Console.WriteLine($"❌ Batteries_V2.Init() 失败: {ex1.Message}");

                try
                {
                    // 方法2: 手动设置提供程序
                    raw.SetProvider(new SQLite3Provider_e_sqlite3());
                    Console.WriteLine("✅ SQLite 原生库初始化成功 (手动设置)");
                }
                catch (Exception ex2)
                {
                    Console.WriteLine($"❌ 手动设置提供程序失败: {ex2.Message}");
                }
            }
        }

        /// <summary>
        /// 测试数据库连接
        /// </summary>
        private void TestConnection()
        {
            try
            {
                OpenConnection();
                using (var command = new SqliteCommand("SELECT sqlite_version();", _connection))
                {
                    var version = command.ExecuteScalar();
                    Console.WriteLine($"✅ SQLite 连接测试成功，版本: {version}");
                }
            }
            catch (Exception ex)
            {
                throw new Exception($"SQLite 连接测试失败: {ex.Message}", ex);
            }
            finally
            {
                CloseConnection();
            }
        }

        /// <summary>
        /// 公共连接测试方法
        /// </summary>
        public void TestConnectionPublic()
        {
            TestConnection();
        }

        /// <summary>
        /// 获取数据库连接
        /// </summary>
        public SqliteConnection GetConnection()
        {
            OpenConnection();
            return _connection;
        }

        /// <summary>
        /// 打开数据库连接
        /// </summary>
        public void OpenConnection()
        {
            if (_connection.State != ConnectionState.Open)
            {
                _connection.Open();
            }
        }

        /// <summary>
        /// 关闭数据库连接
        /// </summary>
        public void CloseConnection()
        {
            if (_connection.State != ConnectionState.Closed)
            {
                _connection.Close();
            }
        }

        /// <summary>
        /// 执行非查询SQL语句（增、删、改）
        /// </summary>
        public int ExecuteNonQuery(string sql, Dictionary<string, object>? parameters = null)
        {
            try
            {
                OpenConnection();
                using (var command = new SqliteCommand(sql, _connection))
                {
                    AddParameters(command, parameters);
                    return command.ExecuteNonQuery();
                }
            }
            catch (Exception ex)
            {
                throw new Exception($"执行SQL语句失败: {sql}", ex);
            }
        }

        /// <summary>
        /// 执行查询并返回DataTable
        /// </summary>
        public DataTable ExecuteDataTable(string sql, Dictionary<string, object>? parameters = null)
        {
            try
            {
                OpenConnection();
                using (var command = new SqliteCommand(sql, _connection))
                {
                    AddParameters(command, parameters);

                    using (var reader = command.ExecuteReader())
                    {
                        var dataTable = new DataTable();
                        dataTable.Load(reader);
                        return dataTable;
                    }
                }
            }
            catch (Exception ex)
            {
                throw new Exception($"执行查询失败: {sql}", ex);
            }
        }

        /// <summary>
        /// 执行查询并返回第一行第一列的值
        /// </summary>
        public object ExecuteScalar(string sql, Dictionary<string, object>? parameters = null)
        {
            try
            {
                OpenConnection();
                using (var command = new SqliteCommand(sql, _connection))
                {
                    AddParameters(command, parameters);
                    var result = command.ExecuteScalar();
                    return result ?? DBNull.Value;
                }
            }
            catch (Exception ex)
            {
                throw new Exception($"执行标量查询失败: {sql}", ex);
            }
        }

        /// <summary>
        /// 执行查询并返回SqliteDataReader
        /// </summary>
        public SqliteDataReader ExecuteReader(string sql, Dictionary<string, object>? parameters = null)
        {
            try
            {
                OpenConnection();
                var command = new SqliteCommand(sql, _connection);
                AddParameters(command, parameters);
                return command.ExecuteReader(CommandBehavior.CloseConnection);
            }
            catch (Exception ex)
            {
                throw new Exception($"执行Reader查询失败: {sql}", ex);
            }
        }

        /// <summary>
        /// 执行查询并返回强类型列表
        /// </summary>
        public List<T> ExecuteList<T>(string sql, Dictionary<string, object>? parameters = null, Func<SqliteDataReader, T>? mapper = null)
        {
            var result = new List<T>();
            using (var reader = ExecuteReader(sql, parameters))
            {
                while (reader.Read())
                {
                    if (mapper != null)
                    {
                        result.Add(mapper(reader));
                    }
                    else
                    {
                        result.Add(SimpleMapper<T>.Map(reader));
                    }
                }
            }
            return result;
        }

        /// <summary>
        /// 批量插入数据（高性能）
        /// </summary>
        public void BulkInsert<T>(string tableName, IEnumerable<T> data, Func<T, Dictionary<string, object>> parameterMapper)
        {
            using (var transaction = BeginTransaction())
            {
                try
                {
                    foreach (var item in data)
                    {
                        var parameters = parameterMapper(item);
                        var columns = string.Join(", ", parameters.Keys);
                        var values = string.Join(", ", parameters.Keys.Select(k => $"@{k}"));
                        var sql = $"INSERT INTO {tableName} ({columns}) VALUES ({values})";

                        using (var command = new SqliteCommand(sql, _connection, transaction))
                        {
                            foreach (var param in parameters)
                            {
                                command.Parameters.AddWithValue($"@{param.Key}", param.Value ?? DBNull.Value);
                            }
                            command.ExecuteNonQuery();
                        }
                    }
                    transaction.Commit();
                }
                catch
                {
                    transaction.Rollback();
                    throw;
                }
            }
        }

        /// <summary>
        /// 检查表是否存在
        /// </summary>
        public bool TableExists(string tableName)
        {
            string sql = "SELECT count(*) FROM sqlite_master WHERE type='table' AND name=@tableName";
            var parameters = new Dictionary<string, object>
            {
                { "@tableName", tableName }
            };

            var result = ExecuteScalar(sql, parameters);
            return Convert.ToInt32(result) > 0;
        }

        /// <summary>
        /// 开始事务
        /// </summary>
        public SqliteTransaction BeginTransaction()
        {
            OpenConnection();
            return _connection.BeginTransaction();
        }

        /// <summary>
        /// 在事务中执行操作
        /// </summary>
        public void ExecuteInTransaction(Action<SqliteTransaction> action)
        {
            using (var transaction = BeginTransaction())
            {
                try
                {
                    action(transaction);
                    transaction.Commit();
                }
                catch
                {
                    transaction.Rollback();
                    throw;
                }
            }
        }

        /// <summary>
        /// 创建表
        /// </summary>
        public void CreateTable(string createTableSql)
        {
            ExecuteNonQuery(createTableSql);
        }

        /// <summary>
        /// 获取最后插入的ID
        /// </summary>
        public long GetLastInsertRowId()
        {
            var result = ExecuteScalar("SELECT last_insert_rowid();");
            return Convert.ToInt64(result);
        }

        /// <summary>
        /// 备份数据库
        /// </summary>
        public void BackupDatabase(string backupPath)
        {
            using (var backupConnection = new SqliteConnection($"Data Source={backupPath}"))
            {
                backupConnection.Open();
                _connection.BackupDatabase(backupConnection);
            }
        }

        /// <summary>
        /// 添加参数到命令
        /// </summary>
        private void AddParameters(SqliteCommand command, Dictionary<string, object>? parameters)
        {
            if (parameters != null)
            {
                foreach (var param in parameters)
                {
                    command.Parameters.AddWithValue(param.Key, param.Value ?? DBNull.Value);
                }
            }
        }

        /// <summary>
        /// 简单的对象映射器
        /// </summary>
        private static class SimpleMapper<T>
        {
            public static T Map(SqliteDataReader reader)
            {
                var obj = Activator.CreateInstance<T>();
                var properties = typeof(T).GetProperties();

                for (int i = 0; i < reader.FieldCount; i++)
                {
                    var fieldName = reader.GetName(i);
                    var property = Array.Find(properties, p =>
                        p.Name.Equals(fieldName, StringComparison.OrdinalIgnoreCase));

                    if (property != null && property.CanWrite && !reader.IsDBNull(i))
                    {
                        var value = reader.GetValue(i);
                        var targetType = Nullable.GetUnderlyingType(property.PropertyType) ?? property.PropertyType;
                        property.SetValue(obj, Convert.ChangeType(value, targetType));
                    }
                }
                return obj;
            }
        }

        public void Dispose()
        {
            Dispose(true);
            GC.SuppressFinalize(this);
        }

        protected virtual void Dispose(bool disposing)
        {
            if (!_disposed)
            {
                if (disposing)
                {
                    _connection?.Close();
                    _connection?.Dispose();
                }
                _disposed = true;
            }
        }

        internal DataTable ExecuteQuery(string sqliteQuery)
        {
            try
            {
                OpenConnection();
                using (var command = new SqliteCommand(sqliteQuery, _connection))
                using (var reader = command.ExecuteReader())
                {
                    var dataTable = new DataTable();
                    dataTable.Load(reader);
                    return dataTable;
                }
            }
            catch (Exception ex)
            {
                throw new Exception($"执行查询失败: {sqliteQuery}", ex);
            }
            finally
            {
                CloseConnection();
            }
        }

        ~SQLiteHelper()
        {
            Dispose(false);
        }
    }
}